<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  
  <meta name="renderer" content="webkit">
  <meta http-equiv="X-UA-Compatible" content="IE=edge" >
  <link rel="dns-prefetch" href="http://woshijingshenbing.com">
  <title>Mysql 随记 | Codreamer的博客</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="description" content="概述不是详细mysql的技术 大全,只是平时工作中会用到,但又总记不住的一些操作,方便自己所取.">
<meta property="og:type" content="article">
<meta property="og:title" content="Mysql 随记">
<meta property="og:url" content="http://woshijingshenbing.com/2016/10/16/Mysql-随记/index.html">
<meta property="og:site_name" content="Codreamer的博客">
<meta property="og:description" content="概述不是详细mysql的技术 大全,只是平时工作中会用到,但又总记不住的一些操作,方便自己所取.">
<meta property="og:image" content="http://static.zybuluo.com/sanyang/zw3vpqtcytctwmz4uf8rj3tw/image_1aq6327b61v4kl7u118fnejrdj9.png">
<meta property="og:updated_time" content="2016-12-21T05:51:42.000Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="Mysql 随记">
<meta name="twitter:description" content="概述不是详细mysql的技术 大全,只是平时工作中会用到,但又总记不住的一些操作,方便自己所取.">
<meta name="twitter:image" content="http://static.zybuluo.com/sanyang/zw3vpqtcytctwmz4uf8rj3tw/image_1aq6327b61v4kl7u118fnejrdj9.png">
  
    <link rel="alternative" href="/atom.xml" title="Codreamer的博客" type="application/atom+xml">
  
  
    <link rel="icon" href="/favicon.png">
  
  <link rel="stylesheet" href="/main.css?v=4.0.0.css">
  

  
<script>
var _hmt = _hmt || [];
(function() {
	var hm = document.createElement("script");
	hm.src = "https://hm.baidu.com/hm.js?45db6f44750c33b1afd7970532c8b3fb";
	var s = document.getElementsByTagName("script")[0]; 
	s.parentNode.insertBefore(hm, s);
})();
</script>


</head>

<body>
  <div id="container" q-class="show:isCtnShow">
    <canvas id="anm-canvas" class="anm-canvas"></canvas>
    <div class="left-col" q-class="show:isShow">
      <div class="overlay"></div>
<div class="intrude-less">
	<header id="header" class="inner">
		<a href="/" class="profilepic">
			<img src="/img/avatar.png" class="js-avatar">
		</a>

		<hgroup>
		  <h1 class="header-author"><a href="/">Codreamer</a></h1>
		</hgroup>

		
		<p class="header-subtitle">除非你改变了交往的人和阅读的书，否则，你的五年之后和现在完全一样</p>
		

		<nav class="header-menu">
			<ul>
			
				<li><a href="/">主页</a></li>
	        
				<li><a href="/photos">相册</a></li>
	        
			</ul>
		</nav>
		<nav class="header-smart-menu">
	        
    		
    			
    			<a data-idx="0" q-on="click: openSlider(e, 'innerArchive')" href="javascript:void(0)">所有文章</a>
    			
    			
            
    			
    			<a data-idx="1" q-on="click: openSlider(e, 'friends')" href="javascript:void(0)">友链</a>
    			
    			
            
    			
    			<a data-idx="2" q-on="click: openSlider(e, 'aboutme')" href="javascript:void(0)">关于我</a>
    			
    			
            
		</nav>
		<nav class="header-nav">
			<div class="social">
				
					<a class="github" target="_blank" href="https://github.com/sniperyen" title="github"><i class="icon-github"></i></a>
		        
					<a class="weibo" target="_blank" href="http://weibo.com/ddhj" title="weibo"><i class="icon-weibo"></i></a>
		        
					<a class="qq" target="_blank" href="/871757525" title="qq"><i class="icon-qq"></i></a>
		        
					<a class="mail" target="_blank" href="mailto:871757525@qq.com" title="mail"><i class="icon-mail"></i></a>
		        
			</div>
		</nav>
	</header>		
</div>

    </div>
    <div class="mid-col" q-class="show:isShow,hide:isShow|isFalse">
      <nav id="mobile-nav">
  	<div class="overlay">
  		<div class="slider-trigger"><i class="icon-sort"></i></div>
  		<h1 class="header-author js-mobile-header hide">Codreamer</h1>
  	</div>
	<div class="intrude-less">
		<header id="header" class="inner">
			<div class="profilepic">
				<img src="/img/avatar.png" class="js-avatar">
			</div>
			<hgroup>
			  <h1 class="header-author">Codreamer</h1>
			</hgroup>
			
			<p class="header-subtitle">除非你改变了交往的人和阅读的书，否则，你的五年之后和现在完全一样</p>
			
			<nav class="header-menu">
				<ul>
				
					<li><a href="/">主页</a></li>
		        
					<li><a href="/photos">相册</a></li>
		        
		        
		        	<li><a href="/archives/">所有文章</a></li>
		        
				</ul>
			</nav>
			<nav class="header-nav">
				<div class="social">
					
						<a class="github" target="_blank" href="https://github.com/sniperyen" title="github"><i class="icon-github"></i></a>
			        
						<a class="weibo" target="_blank" href="http://weibo.com/ddhj" title="weibo"><i class="icon-weibo"></i></a>
			        
						<a class="qq" target="_blank" href="/871757525" title="qq"><i class="icon-qq"></i></a>
			        
						<a class="mail" target="_blank" href="mailto:871757525@qq.com" title="mail"><i class="icon-mail"></i></a>
			        
				</div>
			</nav>
		</header>				
	</div>
</nav>

      <div id="wrapper" class="body-wrap">
        <div class="menu-l">
          <div class="canvas-wrap">
            <canvas data-colors="#eaeaea" data-sectionHeight="100" data-contentId="js-content" id="myCanvas1" class="anm-canvas"></canvas>
          </div>
          <div id="js-content" class="content-ll">
            <article id="post-Mysql-随记" class="article article-type-post " itemscope itemprop="blogPost">
  <div class="article-inner">
    
      <header class="article-header">
        
  
    <h1 class="article-title" itemprop="name">
      Mysql 随记
    </h1>
  

        <a href="/2016/10/16/Mysql-随记/" class="archive-article-date">
  	<time datetime="2016-10-16T13:52:32.000Z" itemprop="datePublished"><i class="icon-calendar icon"></i>2016-10-16</time>
</a>
      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <h2 id="概述"><a href="#概述" class="headerlink" title="概述"></a>概述</h2><p>不是详细mysql的技术 大全,只是平时工作中会用到,但又总记不住的一些操作,方便自己所取.<br><a id="more"></a></p>
<h2 id="基本操作"><a href="#基本操作" class="headerlink" title="基本操作"></a>基本操作</h2><figure class="highlight oxygene"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div></pre></td><td class="code"><pre><div class="line">启动mysql服务:</div><div class="line">&gt;start mysql</div><div class="line">打开MySql交互界面：</div><div class="line">&gt;mysql -u root -p </div><div class="line">Enter password: 你的密码 </div><div class="line"></div><div class="line"><span class="keyword">create</span> database blog <span class="keyword">default</span> charset utf8</div><div class="line">use blog</div><div class="line">show tables</div><div class="line"><span class="keyword">select</span> * <span class="keyword">from</span> book <span class="keyword">desc</span> createtime</div></pre></td></tr></table></figure>
<ul>
<li><p>执行sql文件或脚本</p>
<figure class="highlight sh"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysql -h localhost -uroot -p123456   -B auth_db &lt; F:\hello world\niuzi.sql  <span class="_">-e</span><span class="string">'show tables'</span></div></pre></td></tr></table></figure>
</li>
<li><p>导出数据</p>
<figure class="highlight sh"><table><tr><td class="gutter"><pre><div class="line">1</div></pre></td><td class="code"><pre><div class="line">mysqldump -ueclouduser -peclouduser123 auth_db auth_permission_info&gt;&gt;~/db_sql/auth_permission_info.sql</div></pre></td></tr></table></figure>
</li>
</ul>
<h2 id="创建自定义函数"><a href="#创建自定义函数" class="headerlink" title="创建自定义函数"></a>创建自定义函数</h2><p>在使用MySQL的过程中，mysql自带的函数可能不能完成我们的业务需求，这时就需要自定义函数,例如笔者在开发过程中遇到下面这个问题：<br>mysql表结构如下<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div></pre></td><td class="code"><pre><div class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> <span class="keyword">IF</span> <span class="keyword">EXISTS</span> <span class="string">`test`</span>; </div><div class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> <span class="string">`test`</span> ( </div><div class="line">  <span class="string">`id`</span> <span class="built_in">int</span>(<span class="number">10</span>) <span class="keyword">unsigned</span> <span class="keyword">NOT</span> <span class="literal">NULL</span> AUTO_INCREMENT, </div><div class="line">  <span class="string">`pic`</span> <span class="built_in">varchar</span>(<span class="number">50</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>, </div><div class="line">  <span class="string">`hashcode`</span> <span class="built_in">varchar</span>(<span class="number">16</span>) <span class="keyword">NOT</span> <span class="literal">NULL</span>, </div><div class="line">  PRIMARY <span class="keyword">KEY</span> (<span class="string">`id`</span>) </div><div class="line">) <span class="keyword">ENGINE</span>=<span class="keyword">InnoDB</span> AUTO_INCREMENT=<span class="number">5</span> <span class="keyword">DEFAULT</span> <span class="keyword">CHARSET</span>=utf8; </div><div class="line"> </div><div class="line"><span class="comment">-- ---------------------------- </span></div><div class="line"><span class="comment">-- Records of test </span></div><div class="line"><span class="comment">-- ---------------------------- </span></div><div class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`test`</span> <span class="keyword">VALUES</span> (<span class="string">'1'</span>, <span class="string">'2012120910403250c3fa209bf48.jpg'</span>, <span class="string">'bf8f83818080c0f1'</span>); </div><div class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`test`</span> <span class="keyword">VALUES</span> (<span class="string">'2'</span>, <span class="string">'2012120620430750c092db26557.JPG'</span>, <span class="string">'ff9880f0f680ceff'</span>); </div><div class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`test`</span> <span class="keyword">VALUES</span> (<span class="string">'3'</span>, <span class="string">'2012120619582550c08861eb062.jpg'</span>, <span class="string">'7f7f004f7f7f7c7f'</span>); </div><div class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> <span class="string">`test`</span> <span class="keyword">VALUES</span> (<span class="string">'4'</span>, <span class="string">'2012112911072650b6d16e7f21f.jpg'</span>, <span class="string">'7f7f004f7f7f007f'</span>);</div></pre></td></tr></table></figure></p>
<p>其中pic字段为图片名称，hashcode是图片的感知哈希编码（16进制编码字符串，长度固定16位），用户输入一个hashcode，怎么从数据库中找出满足字符串对应位置的字符不同的个数小于5的记录呢?就像“11001”和“11101”对应位置不同字符不同的个数为1，比如 用户输入”7f7f004f7f7f00af”,那么第三条和第四条记录是满足的，怎么实现呢？如果单纯的依靠mysql自带的函数很难完成，这时就需要建立自定义函数解决。<br>建立自定义函数的过程如下：</p>
<ol>
<li><p>进入mysql命令行<br>mysql&gt;</p>
</li>
<li><p>用delimiter命令来把语句定界符从 ;变为//。这样就允许在程序体用;定界符传递到服务器，而不是被mysql自己来解释。<br>mysql&gt; delimiter //</p>
</li>
<li><p>创建自定义函数</p>
</li>
</ol>
<figure class="highlight"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div></pre></td><td class="code"><pre><div class="line">mysql&gt;CREATE FUNCTION hashDiff( s1 varchar(16), s2 varchar(16))</div><div class="line">-&gt;RETURNS INT</div><div class="line">-&gt;BEGIN</div><div class="line">-&gt;DECLARE diff, x INT;</div><div class="line">-&gt;SET diff =0;</div><div class="line">-&gt;SET x = 0;</div><div class="line">-&gt;WHILE (x  &lt; 16 )  DO</div><div class="line">-&gt;SET x = x+1;</div><div class="line">-&gt;if SUBSTRING(s1, x,1)&lt;&gt;SUBSTRING(s2, x,1) then</div><div class="line">-&gt;set diff=diff+ 1;</div><div class="line">-&gt;end if;</div><div class="line">-&gt;END WHILE;</div><div class="line">-&gt;RETURN diff;</div><div class="line">-&gt;END</div><div class="line">-&gt;//</div><div class="line">mysql&gt;select * from test t where  hashDiff(t.hashcode,'ff9880f0f680ceff')  &lt; 5;</div></pre></td></tr></table></figure>
<h2 id="创建存储过程"><a href="#创建存储过程" class="headerlink" title="创建存储过程"></a>创建存储过程</h2><ol>
<li><p>进入mysql命令行<br>mysql&gt;</p>
</li>
<li><p>用delimiter命令来把语句定界符从 ;变为//。这样就允许在程序体用;定界符传递到服务器，而不是被mysql自己来解释。<br>mysql&gt; delimiter //</p>
</li>
<li><p>创建存储过程</p>
<figure class="highlight"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div></pre></td><td class="code"><pre><div class="line">mysql&gt; delimiter //</div><div class="line">mysql&gt; CREATE PROCEDURE simpleproc (OUT param1 INT)</div><div class="line">    -&gt; BEGIN</div><div class="line">    -&gt;   SELECT COUNT(*) INTO param1 FROM t;</div><div class="line">    -&gt; END</div><div class="line">    -&gt; //</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"> </div><div class="line">mysql&gt; delimiter ;</div><div class="line">mysql&gt; CALL simpleproc(@a);</div><div class="line">Query OK, 0 rows affected (0.00 sec)</div><div class="line"> </div><div class="line"> </div><div class="line">mysql&gt; SELECT @a;</div><div class="line">+------+</div><div class="line">| @a   |</div><div class="line">+------+</div><div class="line">| 3    |</div><div class="line">+------+</div><div class="line">1 row in set (0.00 sec)</div></pre></td></tr></table></figure>
</li>
</ol>
<h2 id="MySQL的语句执行顺序"><a href="#MySQL的语句执行顺序" class="headerlink" title="MySQL的语句执行顺序"></a>MySQL的语句执行顺序</h2><p>MySQL的语句一共分为11步，如下图所标注的那样，最先执行的总是FROM操作，最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表，这个虚拟的表作为一个处理的输入，只是这些虚拟的表对用户来说是透明的，但是只有最后一个虚拟的表才会被作为结果返回。如果没有在语句中指定某一个子句，那么将会跳过相应的步骤。</p>
<p><img src="http://static.zybuluo.com/sanyang/zw3vpqtcytctwmz4uf8rj3tw/image_1aq6327b61v4kl7u118fnejrdj9.png" alt="image_1aq6327b61v4kl7u118fnejrdj9.png-25.5kB"></p>
<p>下面我们来具体分析一下查询处理的每一个阶段</p>
<ol>
<li>FORM: 对FROM的左边的表和右边的表计算笛卡尔积。产生虚表VT1</li>
<li>ON: 对虚表VT1进行ON筛选，只有那些符合<join-condition>的行才会被记录在虚表VT2中。</join-condition></li>
<li>JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, rug from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止。</li>
<li>WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表VT4中。</where-condition></li>
<li>GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5.</li>
<li>CUBE | ROLLUP: 对表VT5进行cube或者rollup操作，产生表VT6.</li>
<li>HAVING： 对虚拟表VT6应用having过滤，只有符合<having-condition>的记录才会被 插入到虚拟表VT7中。</having-condition></li>
<li>SELECT： 执行select操作，选择指定的列，插入到虚拟表VT8中。</li>
<li>DISTINCT： 对VT8中的记录进行去重。产生虚拟表VT9.</li>
<li>ORDER BY: 将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10.</order_by_list></li>
<li>LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回。</li>
</ol>
<h2 id="Python操作MySql"><a href="#Python操作MySql" class="headerlink" title="Python操作MySql"></a>Python操作MySql</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div><div class="line">9</div><div class="line">10</div><div class="line">11</div><div class="line">12</div><div class="line">13</div><div class="line">14</div><div class="line">15</div><div class="line">16</div><div class="line">17</div><div class="line">18</div><div class="line">19</div><div class="line">20</div><div class="line">21</div><div class="line">22</div><div class="line">23</div><div class="line">24</div><div class="line">25</div><div class="line">26</div><div class="line">27</div><div class="line">28</div><div class="line">29</div><div class="line">30</div><div class="line">31</div><div class="line">32</div><div class="line">33</div><div class="line">34</div><div class="line">35</div><div class="line">36</div><div class="line">37</div><div class="line">38</div><div class="line">39</div></pre></td><td class="code"><pre><div class="line"><span class="meta">&gt;&gt;&gt; </span><span class="keyword">import</span> MySQLdb</div><div class="line"><span class="meta">&gt;&gt;&gt; </span>Con = MySQLdb.Connect(host=<span class="string">"127.0.0.1"</span>, port=<span class="number">3306</span>, user=<span class="string">"yourname"</span>, passwd=<span class="string">"yourpwd"</span>, db=<span class="string">"test"</span>)</div><div class="line"><span class="meta">&gt;&gt;&gt; </span>Cur = Con.cursor() <span class="comment"># 如果某个数据库支持事务，会自动开启</span></div><div class="line"><span class="meta">&gt;&gt;&gt; </span>sql = <span class="string">"SELECT top 1 * FROM test.testing"</span></div><div class="line"><span class="meta">&gt;&gt;&gt; </span>Cur.execute(sql)</div><div class="line"><span class="meta">&gt;&gt;&gt; </span>data = Cur.fetchone()</div><div class="line"><span class="meta">&gt;&gt;&gt; </span>Cur.execute(<span class="string">"create table if not exists\</span></div><div class="line">		writers(Id int primary key auto_increment,Name varchar(25))") <span class="comment">#创建了一个数据表 writer(id,name)</span></div><div class="line"><span class="meta">&gt;&gt;&gt; </span>Cur.execute(<span class="string">"insert into writer(Name) values('Jack London')"</span>)</div><div class="line"><span class="meta">&gt;&gt;&gt; </span>Cur.execute(<span class="string">"insert into writer(Name) values('Honore de Balzac')"</span>)     </div><div class="line"><span class="meta">&gt;&gt;&gt; </span>Cur.execute(<span class="string">"insert into writer(Name) values('Lion Feuchtwanger')"</span>)  <span class="comment">#插入了3条数据</span></div><div class="line"><span class="meta">&gt;&gt;&gt; </span>Con.commit() <span class="comment">#事务的特性：手动提交，出错自动回滚</span></div><div class="line"><span class="meta">&gt;&gt;&gt; </span>sql = <span class="string">"SELECT * FROM test.writer"</span></div><div class="line"><span class="meta">&gt;&gt;&gt; </span>Cur.execute(sql)</div><div class="line"><span class="meta">&gt;&gt;&gt; </span>rows = Cur.fetchall() <span class="comment">#将结果集（多维数组）存入rows里面</span></div><div class="line"><span class="meta">&gt;&gt;&gt; </span><span class="keyword">for</span> row <span class="keyword">in</span> rows:</div><div class="line">		<span class="keyword">print</span> row</div><div class="line"><span class="meta">&gt;&gt;&gt; </span>Cur.execute(sql)</div><div class="line"><span class="comment">#如果结果集太大，一下子加载到内存中不合适，这时用下面的方法</span></div><div class="line"><span class="meta">&gt;&gt;&gt; </span>numrows = int(Cur.rowcount)</div><div class="line"><span class="meta">&gt;&gt;&gt; </span><span class="keyword">for</span> i <span class="keyword">in</span> range(numrows): </div><div class="line">		row = Cur.fetchone()</div><div class="line">		<span class="keyword">print</span> row[<span class="number">0</span>], row[<span class="number">1</span>] <span class="comment">#如果这里想用键值访问，例如row["Id"]、row["Name"]，只需在获取cursor时使用如此形式：Cur = Con.cursor(MySQLdb.cursors.DictCursor)</span></div><div class="line"><span class="meta">&gt;&gt;&gt; </span>desc = Cur.description <span class="comment">#获取连接对象的描述信息</span></div><div class="line"><span class="meta">&gt;&gt;&gt; </span><span class="keyword">print</span> <span class="string">"%s %3s"</span> % (desc[<span class="number">0</span>][<span class="number">0</span>], desc[<span class="number">1</span>][<span class="number">0</span>]) <span class="comment">#打印表头，就是字段名称</span></div><div class="line"><span class="meta">&gt;&gt;&gt; </span><span class="comment">#把图片用二进制存入 MYSQL</span></div><div class="line"><span class="meta">&gt;&gt;&gt; </span>Cur.execute(<span class="string">"CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);"</span>) <span class="comment">#创建一张表，用于存放图片</span></div><div class="line"><span class="meta">&gt;&gt;&gt; </span>fin = open(<span class="string">"../web.jpg"</span>)</div><div class="line"><span class="meta">&gt;&gt;&gt; </span>img = fin.read()</div><div class="line"><span class="meta">&gt;&gt;&gt; </span>fin.close()</div><div class="line"><span class="meta">&gt;&gt;&gt; </span>Cur.execute(<span class="string">"INSERT INTO Images SET Data='%s'"</span> % MySQLdb.escape_string(img)) </div><div class="line"><span class="comment">#escape_string函数将插入数据库的字符串进行转义，这会指一些 SQL 注入的攻击 </span></div><div class="line"><span class="meta">&gt;&gt;&gt; </span><span class="comment">#从数据库中把图片读出来</span></div><div class="line"><span class="meta">&gt;&gt;&gt; </span>cursor.execute(<span class="string">"SELECT Data FROM Images LIMIT 1"</span>)</div><div class="line"><span class="meta">&gt;&gt;&gt; </span>fout = open(<span class="string">"image.png"</span>,<span class="string">"wb"</span>)</div><div class="line"><span class="meta">&gt;&gt;&gt; </span>fout.write(cursor.fetchone()[<span class="number">0</span>])</div><div class="line"><span class="meta">&gt;&gt;&gt; </span>fout.close()</div><div class="line"><span class="meta">&gt;&gt;&gt; </span>Cur.close()</div><div class="line"><span class="meta">&gt;&gt;&gt; </span>Con.close()</div></pre></td></tr></table></figure>

      

      
        <div class="page-reward">
          <a href="javascript:;" class="page-reward-btn tooltip-top">
            <div class="tooltip tooltip-east">
            <span class="tooltip-item">
              赏
            </span>
            <span class="tooltip-content">
              <span class="tooltip-text">
                <span class="tooltip-inner">
                  <p class="reward-p"><i class="icon icon-quo-left"></i>谢谢你请我吃糖果<i class="icon icon-quo-right"></i></p>
                  <div class="reward-box">
                    
                    <div class="reward-box-item">
                      <img class="reward-img" src="/img/pay-alipay.png">
                      <span class="reward-type">支付宝</span>
                    </div>
                    
                    
                    <div class="reward-box-item">
                      <img class="reward-img" src="/img/pay-wechat.png">
                      <span class="reward-type">微信</span>
                    </div>
                    
                  </div>
                </span>
              </span>
            </span>
          </div>
          </a>
        </div>
      
    </div>
    <div class="article-info article-info-index">
      
      
	<div class="article-tag tagcloud">
		<i class="icon-price-tags"></i>
		<ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/Mysql/">Mysql</a></li></ul>
	</div>

      
	<div class="article-category tagcloud">
	<i class="icon-book icon"></i>
	<a class="article-category-link" href="/categories/技术/">技术</a>
	</div>


      

      
        
<div class="share-btn share-icons tooltip-left">
  <div class="tooltip tooltip-east">
    <span class="tooltip-item">
      <a href="javascript:;" class="share-sns share-outer">
        <i class="icon icon-share"></i>
      </a>
    </span>
    <span class="tooltip-content">
      <div class="share-wrap">
        <div class="share-icons">
          <a class="weibo share-sns" href="javascript:;" data-type="weibo">
            <i class="icon icon-weibo"></i>
          </a>
          <a class="weixin share-sns wxFab" href="javascript:;" data-type="weixin">
            <i class="icon icon-weixin"></i>
          </a>
          <a class="qq share-sns" href="javascript:;" data-type="qq">
            <i class="icon icon-qq"></i>
          </a>
          <a class="douban share-sns" href="javascript:;" data-type="douban">
            <i class="icon icon-douban"></i>
          </a>
          <a class="qzone share-sns" href="javascript:;" data-type="qzone">
            <i class="icon icon-qzone"></i>
          </a>
          <a class="facebook share-sns" href="javascript:;" data-type="facebook">
            <i class="icon icon-facebook"></i>
          </a>
          <a class="twitter share-sns" href="javascript:;" data-type="twitter">
            <i class="icon icon-twitter"></i>
          </a>
          <a class="google share-sns" href="javascript:;" data-type="google">
            <i class="icon icon-google"></i>
          </a>
        </div>
      </div>
    </span>
  </div>
</div>

<div class="page-modal wx-share js-wx-box">
    <a class="close js-modal-close" href="javascript:;"><i class="icon icon-close"></i></a>
    <p>扫一扫，分享到微信</p>
    <div class="wx-qrcode">
      <img src="http://s.jiathis.com/qrcode.php?url=http://woshijingshenbing.com/2016/10/16/Mysql-随记/" alt="微信分享二维码">
    </div>
</div>

<div class="mask js-mask"></div>
      
      <div class="clearfix"></div>
    </div>
  </div>
</article>

  
<nav id="article-nav">
  
    <a href="/2016/10/16/VIM-随记/" id="article-nav-newer" class="article-nav-link-wrap">
      <i class="icon-circle-left"></i>
      <div class="article-nav-title">
        
          VIM 随记
        
      </div>
    </a>
  
  
    <a href="/2016/10/16/Git-随记/" id="article-nav-older" class="article-nav-link-wrap">
      <div class="article-nav-title">Git 随记</div>
      <i class="icon-circle-right"></i>
    </a>
  
</nav>






<div class="duoshuo">
	<!-- 多说评论框 start -->
	<div class="ds-thread" data-thread-key="Mysql-随记" data-title="Mysql 随记" data-url="http://woshijingshenbing.com/2016/10/16/Mysql-随记/"></div>
	<!-- 多说评论框 end -->
	<!-- 多说公共JS代码 start (一个网页只需插入一次) -->
	<script type="text/javascript">
	var duoshuoQuery = {short_name:"woshijingshenbing"};
	(function() {
		var ds = document.createElement('script');
		ds.type = 'text/javascript';ds.async = true;
		ds.src = (document.location.protocol == 'https:' ? 'https:' : 'http:') + '//static.duoshuo.com/embed.js';
		ds.charset = 'UTF-8';
		(document.getElementsByTagName('head')[0] 
		 || document.getElementsByTagName('body')[0]).appendChild(ds);
	})();
	</script>
	<!-- 多说公共JS代码 end -->
</div>





          </div>
        </div>
      </div>
      <script async src="//dn-lbstatics.qbox.me/busuanzi/2.3/busuanzi.pure.mini.js">
</script>

<footer id="footer">
  <div class="outer">
    <div id="footer-info">
    	<div class="footer-left">
    		&copy; 2016 Codreamer
    	</div>
      	<div class="footer-right">
      	<span id="busuanzi_container_site_uv">
          本站访客数<span id="busuanzi_value_site_uv"></span>人次
        </span>
      	</div>
    </div>
  </div>
</footer>

    </div>
    <script>
	var yiliaConfig = {
		mathjax: false,
		isHome: false,
		isPost: true,
		isArchive: false,
		isTag: false,
		isCategory: false,
		open_in_new: false,
		root: "/",
		innerArchive: true
	}
</script>

<script src="/./main.js?v=4.0.0.js"></script>


    
<div class="tools-col" q-class="show:isShow,hide:isShow|isFalse" q-on="click:stop(e)">
  <div class="tools-wrap">
    
    	<section class="tools-section tools-section-all" q-show="innerArchive">
        <div class="search-wrap">
          <input class="search-ipt" q-model="search" type="text" placeholder="find something…">
          <i class="icon-search icon" q-show="search|isEmptyStr"></i>
          <i class="icon-close icon" q-show="search|isNotEmptyStr" q-on="click:clearChose(e)"></i>
        </div>
        <div class="widget tagcloud search-tag">
          <p class="search-tag-wording">tag:</p>
          <label class="search-switch">
            <input type="checkbox" q-on="click:toggleTag(e)" q-attr="checked:showTags">
          </label>
          <ul class="article-tag-list" q-show="showTags">
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">Git</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">http</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">MongoDB</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">Markdown</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">Mysql</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">VIM</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">定时任务</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">mac</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">hexo</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">nodejs</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">pycharm</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">随记</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">游记</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">马尔代夫</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">买房</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">北京</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">python</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">并发</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">线程</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">进程</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">协程</a>
              </li>
             
              <li class="article-tag-list-item">
                <a href="javascript:void(0)">设计模式</a>
              </li>
            
            <div class="clearfix"></div>
          </ul>
        </div>
        <ul class="search-ul">
          <p q-show="jsonFail" style="padding: 20px; font-size: 12px;">
            缺失模块。<br/>1、在博客根目录（注意不是yilia根目录）执行以下命令：<br/> npm i hexo-generator-json-content --save<br/><br/>
            2、在根目录_config.yml里添加配置：
<pre style="font-size: 12px;" q-show="jsonFail">
  jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: true
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true
</pre>
          </p>
          <li class="search-li" q-repeat="items" q-show="isShow">
            <a q-attr="href:path|urlformat" class="search-title"><i class="icon-quo-left icon"></i><span q-text="title"></span></a>
            <p class="search-time">
              <i class="icon-calendar icon"></i>
              <span q-text="date|dateformat"></span>
            </p>
            <p class="search-tag">
              <i class="icon-price-tags icon"></i>
              <span q-repeat="tags" q-on="click:choseTag(e, name)" q-text="name|tagformat"></span>
            </p>
          </li>
        </ul>
    	</section>
    

    
    	<section class="tools-section tools-section-friends" q-show="friends">
  		
        <ul class="search-ul">
          
            <li class="search-li">
              <a href="https://www.v2ex.com/" target="_blank" class="search-title"><i class="icon-quo-left icon"></i>v2ex</a>
            </li>
          
            <li class="search-li">
              <a href="http://weavesilk.com/" target="_blank" class="search-title"><i class="icon-quo-left icon"></i>weavesilk</a>
            </li>
          
        </ul>
  		
    	</section>
    

    
    	<section class="tools-section tools-section-me" q-show="aboutme">
  	  	
  	  		<div class="aboutme-wrap" id="js-aboutme">他真诚地将自己的挑剔,&lt;br /&gt;误认为是对美的不懈追求，&lt;br /&gt;把自己的孤僻,&lt;br /&gt;误认为是不食人间烟火，&lt;br /&gt;把自己的散漫,&lt;br /&gt;误认为是对于自由的热爱。&lt;br /&gt;&lt;br /&gt;他的思想因为追求风雅而变得庸俗起来，&lt;br /&gt;他的行为因为小心翼翼而变得放纵不羁。&lt;br /&gt;&lt;br /&gt;他有很强的责任感，&lt;br /&gt;却也因此害怕做出承诺。&lt;br /&gt;&lt;br /&gt;他批判世上的所有东西，&lt;br /&gt;包括他自己。&lt;br /&gt;&lt;br /&gt;当别人指出他的自相矛盾后，&lt;br /&gt;他无法自圆其说，&lt;br /&gt;于是只好沉默。&lt;br /&gt;&lt;br /&gt;他是个不可理喻的人。</div>
  	  	
    	</section>
    
  </div>
  
</div>
    <!-- Root element of PhotoSwipe. Must have class pswp. -->
<div class="pswp" tabindex="-1" role="dialog" aria-hidden="true">

    <!-- Background of PhotoSwipe. 
         It's a separate element as animating opacity is faster than rgba(). -->
    <div class="pswp__bg"></div>

    <!-- Slides wrapper with overflow:hidden. -->
    <div class="pswp__scroll-wrap">

        <!-- Container that holds slides. 
            PhotoSwipe keeps only 3 of them in the DOM to save memory.
            Don't modify these 3 pswp__item elements, data is added later on. -->
        <div class="pswp__container">
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
        </div>

        <!-- Default (PhotoSwipeUI_Default) interface on top of sliding area. Can be changed. -->
        <div class="pswp__ui pswp__ui--hidden">

            <div class="pswp__top-bar">

                <!--  Controls are self-explanatory. Order can be changed. -->

                <div class="pswp__counter"></div>

                <button class="pswp__button pswp__button--close" title="Close (Esc)"></button>

                <button class="pswp__button pswp__button--share" style="display:none" title="Share"></button>

                <button class="pswp__button pswp__button--fs" title="Toggle fullscreen"></button>

                <button class="pswp__button pswp__button--zoom" title="Zoom in/out"></button>

                <!-- Preloader demo http://codepen.io/dimsemenov/pen/yyBWoR -->
                <!-- element will get class pswp__preloader--active when preloader is running -->
                <div class="pswp__preloader">
                    <div class="pswp__preloader__icn">
                      <div class="pswp__preloader__cut">
                        <div class="pswp__preloader__donut"></div>
                      </div>
                    </div>
                </div>
            </div>

            <div class="pswp__share-modal pswp__share-modal--hidden pswp__single-tap">
                <div class="pswp__share-tooltip"></div> 
            </div>

            <button class="pswp__button pswp__button--arrow--left" title="Previous (arrow left)">
            </button>

            <button class="pswp__button pswp__button--arrow--right" title="Next (arrow right)">
            </button>

            <div class="pswp__caption">
                <div class="pswp__caption__center"></div>
            </div>

        </div>

    </div>

</div>
  </div>
</body>
</html>